﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using ModelLayer;
/// <summary>
/// Dataaccess class for SeatBooking
/// </summary>
namespace DBLayer
{
    public class SeatBookingDB
    {
        //Variabler initialization
        private SqlConnection con;
        private SqlUtility sqlUtility;
        private FlightInstanceDB flightInstanceDb;
        private FlightDB flightDb;
        private SeatDB seatDb;
        private PlaneDB planeDb;
 
        public SeatBookingDB()
        {
            sqlUtility = new SqlUtility();
            flightInstanceDb = new FlightInstanceDB();
            flightDb = new FlightDB();
            seatDb = new SeatDB();
            planeDb = new PlaneDB();
        }
        private int CountDatabase()
        {
            int count;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "Select COUNT (SeatbookingId) from Seatbooking";
                SqlCommand cmd = new SqlCommand(query, con);
                count = (int)cmd.ExecuteScalar();
            }
            con.Close();
            con.Dispose();
            return count;
        }
        //Insert Flight into database 
        public int InsertSeatBooking(SeatBooking _seatBooking, int _travelId)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            int count = CountDatabase() + 1;
            const string query =
                "INSERT INTO SeatBooking (seatBookingId, flightInstanceId, seatNo, checkedIn, travelId, planeNo) VALUES" +
                " (@seatBookingId, @flightInstanceId, @seatNo, @checkedIn, @travelId, @planeNo)";
            
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@seatBookingId", count);
            cmd.Parameters.AddWithValue("@flightInstanceId", _seatBooking.FlightInstance.FlightInstanceId);
            cmd.Parameters.AddWithValue("@seatNo", _seatBooking.Seat.SeatNo);
            cmd.Parameters.AddWithValue("@checkedIn", _seatBooking.CheckedIn);
            cmd.Parameters.AddWithValue("@travelId", _travelId);
            cmd.Parameters.AddWithValue("@planeNo", _seatBooking.FlightInstance.Plane.PlaneNo);

            return sqlUtility.ExecuteData(cmd, connect);
        }

        public SeatBooking FindSeatBookByFlightAndSeat(FlightInstance _flightInstance, Seat _seat)
        {
            bool exists = false;
            SeatBooking retSeatBooking = null;
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from SeatBooking WHERE flightInstanceId = @flightInstanceId and seatNo = @seatNo";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    cmd.Parameters.AddWithValue("@flightInstanceId", _flightInstance.FlightInstanceId);
                    cmd.Parameters.AddWithValue("@seatNo", _seat.SeatNo);
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            retSeatBooking = BuildSeatBooking(rdr);
                            exists = true;
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            if(exists)
            {
                retSeatBooking.FlightInstance = flightInstanceDb.FindFlightInstanceById(retSeatBooking.FlightInstance.FlightInstanceId);
                retSeatBooking.Seat = seatDb.FindSeatBySeatNoAndPlaneNo(retSeatBooking.Seat.SeatNo, retSeatBooking.FlightInstance.Plane.PlaneNo);
            }
            return retSeatBooking;
        }
        //Update SeatBooking in database by object SeatBooking
        public int UpdateSeatBooking(SeatBooking _seatBooking)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
           const string query = "UPDATE SeatBooking SET seatBookingId = @seatBookingId, flightInstanceId = @flightInstanceId, " +
                                "seatNo = @seatNo, checkedIn = @checkedIn, travelId = @travelId, planeNo = @planeNo WHERE seatBookingId = @seatBookingId";
                
            SqlCommand cmd = new SqlCommand(query, connect);
            cmd.Parameters.AddWithValue("@seatBookingId", _seatBooking.SeatBookingId);
            cmd.Parameters.AddWithValue("@flightInstanceId", _seatBooking.FlightInstance.FlightInstanceId);
            cmd.Parameters.AddWithValue("@seatNo", _seatBooking.Seat.SeatNo);
            cmd.Parameters.AddWithValue("@checkedIn", _seatBooking.CheckedIn);
            cmd.Parameters.AddWithValue("@planeNo", _seatBooking.FlightInstance.Plane.PlaneNo);

            return sqlUtility.ExecuteData(cmd,connect);
        }
        //build object SeatBooking from data from database
        private SeatBooking BuildSeatBooking(SqlDataReader _reader)
        {
            SeatBooking temSeatBooking = new SeatBooking();

            temSeatBooking.SeatBookingId = (int) _reader["seatBookingId"];
            temSeatBooking.Seat = new Seat();
            temSeatBooking.Seat.SeatNo = (string) _reader["seatNo"];
            temSeatBooking.CheckedIn = (bool) _reader["checkedIn"];
            temSeatBooking.FlightInstance = new FlightInstance();
            //temSeatBooking.FlightInstance.Flight = new Flight();
            temSeatBooking.FlightInstance.FlightInstanceId = (int)_reader["flightInstanceId"];

            return temSeatBooking;
        }
        //remove SeatBooking in database by object 
        public int RemoveSeatBooking(SeatBooking _seatBooking)
        {
            SqlConnection connect = sqlUtility.GetCon();
            connect.Open();
            string query = "DELETE from SeatBooking WHERE seatBookingId = @seatBookingId";
            SqlCommand cmd = new SqlCommand(query, con);
            cmd.Parameters.AddWithValue("@seatBookingId", _seatBooking.SeatBookingId);
            return sqlUtility.ExecuteData(cmd,connect);
        }

        //return a List<SeatBooking> with all the SeatBooking in the database
        public List<SeatBooking> SelectAllSeatBookings()
        {
            List<SeatBooking> seatBookingList = new List<SeatBooking>();
            using (con = sqlUtility.GetCon())
            {
                con.Open();
                string query = "SELECT * from SeatBooking";
                using (SqlCommand cmd = new SqlCommand(query, con))
                {
                    using (SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                            SeatBooking temSeatBooking = new SeatBooking();
                            temSeatBooking = BuildSeatBooking(rdr);
                            seatBookingList.Add(temSeatBooking);
                        }
                        rdr.Close();
                        rdr.Dispose();
                    }
                    cmd.Dispose();
                }
                con.Close();
                con.Dispose();
            }
            foreach (SeatBooking sb in seatBookingList)
            {
                int routeId = sb.FlightInstance.Flight.RouteId;

                sb.FlightInstance = flightInstanceDb.FindFlightInstanceById(routeId);
                sb.FlightInstance.Flight = flightDb.FindFlightByID(routeId);
            }
            return seatBookingList;
        }
     
    }
}



